﻿Imports System.Text.RegularExpressions

''' <summary>
''' 提供與资料庫相關的作業處理 , 包含 select ,insert , update , delete 
''' </summary>
''' <remarks></remarks>
Public Class clsDataSrc

    Private oDB As clsDBUtil

    ''' <summary>
    ''' Begin Transaction
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub BeginTrans()
        Try
            oDB.BeginTrans()
        Catch ex As Exception

        End Try

    End Sub

    ''' <summary>
    ''' Commit Transaction
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Commit()
        Try
            oDB.Commit()
        Catch ex As Exception

        End Try

    End Sub

    ''' <summary>
    ''' Rollback Transaction
    ''' </summary>
    ''' <remarks></remarks>
    Public Sub Rollback()
        Try
            oDB.RollbackTrans()
        Catch ex As Exception

        End Try
    End Sub
    ''' <summary>
    ''' 找出會員ID
    ''' </summary>
    ''' <param name="verify"></param>
    ''' <param name="verifytype"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function FindID(ByVal verify As String, ByVal verifytype As Integer) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim sID As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter

        ReDim oParam(1)
        oParam(0) = New SqlClient.SqlParameter
        oParam(0).ParameterName = "@verify"
        oParam(0).Value = verify
        oParam(1) = New SqlClient.SqlParameter
        oParam(1).ParameterName = "@verifytype"
        oParam(1).Value = verifytype
        Try
            If verifytype = 0 Then
                sSQL = "select ID from accounts with (nolock)  where email = @verify"
            Else
                sSQL = "select ID from accounts with (nolock)  where mobile_no = @verify"
            End If

            dt = oDB.ExecuteDT(sSQL, oParam)

            If dt.Rows.Count > 0 Then
                sID = dt.Rows(0).Item("ID")
            End If
            Return sID
        Catch ex As Exception
            Return ex.ToString()
        End Try

    End Function

    ''' <summary>
    ''' 取得會員姓名
    ''' </summary>
    ''' <param name="verifytype"></param>
    ''' <param name="verifyvalue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GETCNAME(ByVal verifytype As Integer, ByVal verifyvalue As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim sCNAME As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            If (verifytype = 0) Then
                sSQL = "SELECT CNAME FROM ACCOUNTS with (nolock)  WHERE EMAIL = @email"
                ReDim oParam(0)
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@email"
                oParam(0).Value = verifyvalue
                dt = oDB.ExecuteDT(sSQL, oParam)
                If dt.Rows.Count > 0 Then
                    sCNAME = dt.Rows(0).Item("CNAME")
                End If
                strRt = sCNAME
            Else
                sSQL = "SELECT CNAME FROM ACCOUNTS with (nolock)  WHERE MOBILE_NO = @mobileno"
                ReDim oParam(0)
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@mobileno"
                oParam(0).Value = verifyvalue
                dt = oDB.ExecuteDT(sSQL, oParam)
                If dt.Rows.Count > 0 Then
                    sCNAME = dt.Rows(0).Item("CNAME")
                End If
                strRt = sCNAME
            End If
        Catch ex As Exception
            Return ex.ToString()
        End Try
        Return strRt
    End Function

    ''' <summary>
    ''' 取得會員姓名
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GETCNAME(ByVal id As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim sCNAME As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            ReDim oParam(0)
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = id
            sSQL = "select CNAME from ACCOUNTS  with (nolock)  where ID = @id"
            dt = oDB.ExecuteDT(sSQL, oParam)
            If dt.Rows.Count > 0 Then
                sCNAME = dt.Rows(0).Item("CNAME")
            End If
            strRt = sCNAME
        Catch ex As Exception
            Return ex.ToString()
        End Try
        Return strRt
    End Function

    ''' <summary>
    ''' 依照會員認證型態取得EMAIL或是手机號碼
    ''' </summary>
    ''' <param name="id"></param>
    ''' <param name="verifytype"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GETVERIFYVALUE(ByVal id As String, ByVal verifytype As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim sVERIFYVALUE As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter

        Try
            If (verifytype = 0) Then
                sSQL = "select EMAIL from ACCOUNTS  with (nolock)  where ID = @id"
                ReDim oParam(0)
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@id"
                oParam(0).Value = id
                dt = oDB.ExecuteDT(sSQL, oParam)
                If dt.Rows.Count > 0 Then
                    sVERIFYVALUE = dt.Rows(0).Item("EMAIL")
                End If
                strRt = sVERIFYVALUE
            Else
                sSQL = "select MOBILE_NO from ACCOUNTS  with (nolock)  where ID = @id"
                ReDim oParam(0)
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@id"
                oParam(0).Value = id
                dt = oDB.ExecuteDT(sSQL, oParam)
                If dt.Rows.Count > 0 Then
                    sVERIFYVALUE = dt.Rows(0).Item("MOBILE_NO")
                End If
                strRt = sVERIFYVALUE
            End If
            Return strRt
        Catch ex As Exception
            Return ex.ToString()
        End Try
    End Function

    ''' <summary>
    ''' 取得會員認證方式(EMAIL或手机)
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GETVERIFYTYPE(ByVal id As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim sVERIFYTYPE As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            sSQL = "select VERIFY_TYPE from ACCOUNTS  with (nolock)  where ID = @id"
            ReDim oParam(0)
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = id
            dt = oDB.ExecuteDT(sSQL, oParam)
            If dt.Rows.Count > 0 Then
                sVERIFYTYPE = dt.Rows(0).Item("VERIFY_TYPE")
            End If
            strRt = sVERIFYTYPE
        Catch ex As Exception
            Return ex.ToString()
        End Try
        Return strRt
    End Function

    ''' <summary>
    ''' 取得會員基本资料
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GETMemberProfile(ByVal id As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            sSQL = "select CNAME,EMAIL,MOBILE_NO,SEX,BIRTHDAY from ACCOUNTS  with (nolock)  where ID = @id"
            ReDim oParam(0)
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = id
            dt = oDB.ExecuteDT(sSQL, oParam)
            If dt.Rows.Count > 0 Then
                strRt = dt.Rows(0).Item("CNAME")
                strRt = strRt & "," & dt.Rows(0).Item("EMAIL")
                strRt = strRt & "," & dt.Rows(0).Item("MOBILE_NO")
                strRt = strRt & "," & dt.Rows(0).Item("SEX")
                strRt = strRt & "," & dt.Rows(0).Item("BIRTHDAY")
            End If
        Catch ex As Exception
            Return ex.ToString()
        End Try
        Return strRt
    End Function

    ''' <summary>
    ''' 會員登入失敗後記錄登入時間及IP
    ''' </summary>
    ''' <param name="id">會員ID</param>
    ''' <param name="fail">登入失敗次數</param>
    ''' <param name="usrip">IP位址</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function UPDATE_TIME_FOR_LOGONFAIL(ByVal id As String, ByVal fail As Int16, ByVal usrip As String)
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            If (fail > 3) Then
                sSQL = "UPDATE ACCOUNTS SET IS_LOCKED = 'Y' "
                sSQL = sSQL & " WHERE ID = @id"
                ReDim oParam(0)
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@id"
                oParam(0).Value = id
            Else
                ReDim oParam(1)
                sSQL = "UPDATE ACCOUNTS set LOGONFAIL = LOGONFAIL+1 ,UPDATE_TIME = getdate(),UPDATE_IP = @usrip "
                sSQL = sSQL & " WHERE ID = @id "
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@id"
                oParam(0).Value = id
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@usrip"
                oParam(1).Value = usrip
            End If
            strRt = oDB.ExecuteNonQuery(sSQL, oParam)
            If strRt <> "0" Then
                Throw New Exception(strRt)
            End If

            Return strRt
        Catch ex As Exception
            Return ex.ToString
        End Try
    End Function

    ''' <summary>
    ''' 寫入LOGIN_RECORD並產生TOKEN
    ''' </summary>
    ''' <param name="id">會員ID</param>
    ''' <param name="logintype">登入類別</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function INSERT_LOGIN_RECORD(ByVal id As String, ByVal logintype As String)
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            ReDim oParam(1)
            sSQL = "insert into LOGIN_RECORD (ACCOUNT_ID,LOGIN_TYPE,LOGIN_TIME,EXPIRE_TIME) "
            If (logintype = "WEB") Then
                sSQL = sSQL & " Values (@id,@logintype,GETDATE(),DATEADD(HOUR,1,GETDATE())) "
            Else
                sSQL = sSQL & " Values (@id,@logintype,GETDATE(),DATEADD(DAY,30,GETDATE())) "
            End If

            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = id
            oParam(1) = New SqlClient.SqlParameter
            oParam(1).ParameterName = "@logintype"
            oParam(1).Value = logintype
            strRt = oDB.ExecuteNonQuery(sSQL, oParam)
            If strRt <> "0" Then
                Throw New Exception(strRt)
            End If
            Return strRt
        Catch ex As Exception
            Return ex.ToString()
        End Try
    End Function

    ''' <summary>
    ''' 會員登入成功後記錄登入時間及IP
    ''' </summary>
    ''' <param name="id"></param>
    ''' <param name="usrip"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function UPDATE_TIME_FOR_LOGON(ByVal id As String, ByVal usrip As String)
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            ReDim oParam(1)
            sSQL = "UPDATE ACCOUNTS set LOGONFAIL = 0 , UPDATE_TIME = getdate() , UPDATE_IP = @usrip "
            sSQL = sSQL & "WHERE ID = @id"
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = id
            oParam(1) = New SqlClient.SqlParameter
            oParam(1).ParameterName = "@usrip"
            oParam(1).Value = usrip
            strRt = oDB.ExecuteNonQuery(sSQL, oParam)
            If strRt <> "0" Then
                Throw New Exception(strRt)
            End If

            Return strRt
        Catch ex As Exception
            Return ex.ToString
        End Try
    End Function

    ''' <summary>
    ''' 會員登入
    ''' </summary>
    ''' <param name="verifytype"></param>
    ''' <param name="verifyvalue"></param>
    ''' <param name="Pwd"></param>
    ''' <param name="UsrIP"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function MemberLogon(ByVal verifytype As Integer, ByVal verifyvalue As String, ByVal Pwd As String, ByVal logintype As String, ByVal UsrIP As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim sID As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            '先找出會員ID
            sID = FindID(verifyvalue, verifytype)
            sSQL = "select ID,PASSWORD,STATUS,NEED_RENEW,IS_LOCKED,LOGONFAIL "
            sSQL = sSQL & "FROM ACCOUNTS  with (nolock) "
            sSQL = sSQL & "WHERE ID = @id"
            ReDim oParam(0)
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = sID
            dt = oDB.ExecuteDT(sSQL, oParam)
            If dt.Rows.Count > 0 Then
                '1.先檢查會員啟用狀態
                If (dt.Rows(0).Item("STATUS") <> 1) Then
                    strRt = "1"  '帳號未激活
                    Return strRt
                End If


                '3.檢查帳號是否被鎖定
                If (dt.Rows(0).Item("IS_LOCKED") <> "N") Then
                    strRt = "3"  '帳號被鎖定
                    Return strRt
                End If
                '4.比對密碼是否正確
                Dim sPwd = dt.Rows(0).Item("PASSWORD").ToString().Trim()

                If (sPwd = Pwd.Trim()) Then
                    strRt = UPDATE_TIME_FOR_LOGON(sID, UsrIP)
                    strRt = Login_ExpireTime_Check(sID, logintype)
                    If (strRt = "1" Or strRt = "") Then
                        strRt = INSERT_LOGIN_RECORD(sID, logintype)
                    End If
                    '2.檢查密碼是否需要更新
                    If (dt.Rows(0).Item("NEED_RENEW") <> " ") Then
                        If (dt.Rows(0).Item("NEED_RENEW") <> 0) Then
                            strRt = "2"  '密碼需要更新
                        Else
                            strRt = "0"  '密碼正確
                        End If
                    End If

                Else
                    strRt = UPDATE_TIME_FOR_LOGONFAIL(sID, dt.Rows(0).Item("LOGONFAIL"), UsrIP)
                    strRt = "-3"  '密碼錯誤
                    Return strRt
                End If
            Else
                strRt = "5"
            End If

            Return strRt
        Catch ex As Exception
            Return ex.ToString()
        End Try
    End Function

    ''' <summary>
    ''' 檢查帳號是否已激活
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ACCOUNT_STATUS_CHECK(ByVal id As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim oLog As New clsLog()
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            sSQL = "select STATUS "
            sSQL = sSQL & "FROM ACCOUNTS  with (nolock) "
            sSQL = sSQL & "WHERE ID = @id"
            ReDim oParam(0)
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = id.ToString()
            oLog.writeLine("重送验证码", "", "", "ACCOUNT_STATUS_CHECK", APUtil.clsLog.enumMsgType.General, "SQL:" & sSQL & ",ID:" & id & ",IDTYPE:" & id.GetType().BaseType().ToString())

            dt = oDB.ExecuteDT(sSQL, oParam)
            If dt.Rows.Count > 0 Then

                strRt = dt.Rows(0).Item("STATUS")

            End If
        Catch ex As Exception
            Return strRt = ex.ToString()
        End Try
        Return strRt
    End Function

    Function IsValidEmail(ByVal strIn As String) As Boolean
        ' Return true if strIn is in valid e-mail format.
        Return Regex.IsMatch(strIn, _
               "^([\w-\.]+)@((\[[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.)|(([\w-]+\.)+))([a-zA-Z]{2,4}|[0-9]{1,3})(\]?)$")
    End Function

    Public Function ACCOUNT_IS_EXIST(ByVal REFERER_info As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = "0"
        Dim oLog As New clsLog()
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        oLog.writeLine("ACCOUNT_IS_EXIST", "", "", "ACCOUNT_IS_EXIST", APUtil.clsLog.enumMsgType.General, "SQL:" & sSQL & ",REFERER_info:" & REFERER_info & ",REFERER_info_TYPE:" & REFERER_info.GetType().BaseType().ToString())
        Try
            If (IsValidEmail(REFERER_info)) Then
                sSQL = "select EMAIL "
                sSQL = sSQL & "FROM ACCOUNTS  with (nolock) "
                sSQL = sSQL & "WHERE email = @referer_info"
                ReDim oParam(0)
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@referer_info"
                oParam(0).Value = REFERER_info.ToString()
                dt = oDB.ExecuteDT(sSQL, oParam)
                If dt.Rows.Count > 0 Then
                    strRt = dt.Rows(0).Item("EMAIL")
                End If
            Else
                sSQL = "select EMAIL "
                sSQL = sSQL & "FROM ACCOUNTS  with (nolock) "
                sSQL = sSQL & "WHERE mobile_no = @referer_info"
                ReDim oParam(0)
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@referer_info"
                oParam(0).Value = REFERER_info.ToString()
                dt = oDB.ExecuteDT(sSQL, oParam)
                If dt.Rows.Count > 0 Then
                    strRt = dt.Rows(0).Item("EMAIL")
                End If
            End If
            Return strRt
        Catch ex As Exception
            Return strRt = ex.ToString()
        End Try
    End Function


    ''' <summary>
    ''' 檢查帳號是否已鎖定
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ACCOUNT_ISLOCKED_CHECK(ByVal id As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            sSQL = "select IS_LOCKED "
            sSQL = sSQL & "FROM ACCOUNTS  with (nolock) "
            sSQL = sSQL & "WHERE ID = @id"
            ReDim oParam(0)
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = id
            dt = oDB.ExecuteDT(sSQL, oParam)
            If dt.Rows.Count > 0 Then

                strRt = dt.Rows(0).Item("IS_LOCKED")

            End If
        Catch ex As Exception
            Return strRt = ex.ToString()
        End Try
        Return strRt
    End Function

    Public Function Login_ExpireTime_Check(ByVal id As String, ByVal logintype As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Dim dtNow = DateTime.Now
        sSQL = "select EXPIRE_TIME from LOGIN_RECORD where ACCOUNT_ID = @id and LOGIN_TYPE = @logintype "
        sSQL = sSQL & "order by EXPIRE_TIME desc"
        ReDim oParam(1)
        oParam(0) = New SqlClient.SqlParameter
        oParam(0).ParameterName = "@id"
        oParam(0).Value = id
        oParam(1) = New SqlClient.SqlParameter
        oParam(1).ParameterName = "@logintype"
        oParam(1).Value = logintype
        dt = oDB.ExecuteDT(sSQL, oParam)
        If dt.Rows.Count > 0 Then
            '1.檢查是否在時限內
            If (dtNow > dt.Rows(0).Item("EXPIRE_TIME")) Then
                strRt = "1" '已逾時限
            Else
                strRt = "0"
            End If
        End If
        Return strRt

    End Function

    ''' <summary>
    ''' 檢查激活時限及取得次數
    ''' </summary>
    ''' <param name="id"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function EXPIRETIME_CHECK(ByVal id As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Dim dtNow = DateTime.Now

        sSQL = "select ACCOUNT_ID,VERIFY_CODE,EXPIRE_TIME,RESEND_VERIFYCODE "
        sSQL = sSQL & "FROM ACCOUNT_VERIFY with (nolock)  "
        sSQL = sSQL & "WHERE ACCOUNT_ID = @id"
        ReDim oParam(0)
        oParam(0) = New SqlClient.SqlParameter
        oParam(0).ParameterName = "@id"
        oParam(0).Value = id
        dt = oDB.ExecuteDT(sSQL, oParam)
        If dt.Rows.Count > 0 Then
            '1.檢查是否在激活時限內
            If (dtNow > dt.Rows(0).Item("EXPIRE_TIME")) Then
                strRt = "0" '已逾激活時限,可重新申請驗證碼,不需確認次數
            ElseIf (dt.Rows(0).Item("RESEND_VERIFYCODE").ToString() > 1) Then
                strRt = "2" '已逾重發次數
            Else
                strRt = "1" & "," & dt.Rows(0).Item("VERIFY_CODE").ToString().Trim() '未超過重發次數
            End If
        End If
        Return strRt
    End Function



    ''' <summary>
    ''' 重發驗證碼
    ''' </summary>
    ''' <param name="id"></param>
    ''' <param name="checkcode"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CheckCodeReSend(ByVal id As String, ByVal checkcode As String, ByVal sCheckResult As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            '已逾激活時限,可重新申請驗證碼
            If (sCheckResult = "0") Then
                ReDim oParam(1)
                sSQL = "UPDATE ACCOUNT_VERIFY SET VERIFY_CODE = @checkcode,RESEND_VERIFYCODE = 0,EXPIRE_TIME = DATEADD(DAY,1,GETDATE()) WHERE ACCOUNT_ID = @id  "
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@id"
                oParam(0).Value = id
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@checkcode"
                oParam(1).Value = checkcode
                strRt = oDB.ExecuteNonQuery(sSQL, oParam)
                If strRt <> "0" Then
                    Throw New Exception(strRt)
                End If
            End If

            If (sCheckResult = "1") Then
                ReDim oParam(1)
                sSQL = "UPDATE ACCOUNT_VERIFY SET VERIFY_CODE = @checkcode,RESEND_VERIFYCODE = RESEND_VERIFYCODE+1 WHERE ACCOUNT_ID = @id  "
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@id"
                oParam(0).Value = id
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@checkcode"
                oParam(1).Value = checkcode
                strRt = oDB.ExecuteNonQuery(sSQL, oParam)
                If strRt <> "0" Then
                    Throw New Exception(strRt)
                End If
            End If

        Catch ex As Exception

        End Try

        Return strRt
    End Function


    ''' <summary>
    ''' 重設密碼
    ''' </summary>
    ''' <param name="verifytype"></param>
    ''' <param name="verifyvalue"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ReSetPwd(ByVal id As String, ByVal newPwd As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim sID As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Try
            ''先找出會員ID
            'sID = FindID(verifyvalue, verifytype)
            sID = id
            ReDim oParam(1)
            sSQL = "UPDATE ACCOUNTS set PASSWORD =  @newpwd,NEED_RENEW = 1,UPDATE_TIME = GETDATE() "
            sSQL = sSQL & "WHERE ID = @id"
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = sID
            oParam(1) = New SqlClient.SqlParameter
            oParam(1).ParameterName = "@newpwd"
            oParam(1).Value = newPwd
            strRt = oDB.ExecuteNonQuery(sSQL, oParam)
            If strRt <> "0" Then
                Throw New Exception(strRt)
            End If
            Return "0"
        Catch ex As Exception
            Return ex.ToString()
        End Try

    End Function

    ''' <summary>
    ''' 會員激活
    ''' </summary>
    ''' <param name="verifytype"></param>
    ''' <param name="verifyvalue"></param>
    ''' <param name="checkcode"></param>
    ''' <param name="verifyip"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ActiveMember(ByVal verifytype As Integer, ByVal verifyvalue As String, ByVal checkcode As String, ByVal verifyip As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim sID As String = String.Empty
        Dim dt As New DataTable
        Dim dtNow = DateTime.Now

        Dim oParam() As SqlClient.SqlParameter

        Try
            '先找出會員ID
            sID = FindID(verifyvalue, verifytype)

            ReDim oParam(0)
            sSQL = "select A.ID, B.ACCOUNT_ID, A.STATUS, B.VERIFY_CODE, B.EXPIRE_TIME "
            sSQL = sSQL & "FROM ACCOUNTS AS A with (nolock) INNER JOIN "
            sSQL = sSQL & "ACCOUNT_VERIFY AS B with (nolock) ON A.ID = B.ACCOUNT_ID "
            sSQL = sSQL & "WHERE A.ID = @id"
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = sID
            dt = oDB.ExecuteDT(sSQL, oParam)

            If dt.Rows.Count > 0 Then
                '1.先檢查會員啟用狀態
                If (dt.Rows(0).Item("status") <> 0) Then
                    strRt = dt.Rows(0).Item("status")
                    Return strRt
                End If
                '2.檢查是否在激活時限內
                If (dtNow > dt.Rows(0).Item("expire_time")) Then
                    Return "2" '已逾激活時限,請重新申請驗證碼
                End If
                '3.檢查驗證碼是否正確
                If dt.Rows(0).Item("verify_code") <> checkcode Then
                    Return "3" '驗證碼錯誤
                End If
                '4.更新會員啟用狀態
                ReDim oParam(1)
                sSQL = "UPDATE ACCOUNTS set STATUS=1,VERIFY_DATE = getdate(),UPDATE_TIME = getdate(),VERIFY_TYPE = @verifytype "
                sSQL = sSQL & "WHERE ID = @id"
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@verifytype"
                oParam(0).Value = verifytype
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@id"
                oParam(1).Value = sID
                strRt = oDB.ExecuteNonQuery(sSQL, oParam)
                If strRt <> "0" Then
                    Throw New Exception(strRt)
                End If

                '5.更新ACCOUNT_VERIFY_TIME及VERIFY_IP
                ReDim oParam(1)
                sSQL = "UPDATE ACCOUNT_VERIFY set VERIFY_TIME = getdate() , VERIFY_IP = @verifyip,RESEND_VERIFYCODE = 0 "
                sSQL = sSQL & "WHERE ACCOUNT_ID = @id"
                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@verifyip"
                oParam(0).Value = verifyip
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@id"
                oParam(1).Value = sID
                strRt = oDB.ExecuteNonQuery(sSQL, oParam)
                If strRt <> "0" Then
                    Throw New Exception(strRt)
                End If
                Return "0"
            Else
                Return "-2"
            End If

        Catch ex As Exception
            Return ex.ToString()
        End Try

    End Function

    ''' <summary>
    ''' 產生會員激活驗證碼
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GenerateCheckCode(ByVal verify As String, ByVal verifytype As Integer, ByVal checkcode As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim sID As String = String.Empty
        Dim dt As New DataTable
        Dim oParam() As SqlClient.SqlParameter
        Dim dtExpire_time = DateTime.Now
        Try
            '先找出會員ID
            sID = FindID(verify, verifytype)
            If (verifytype = 0) Then
                dtExpire_time = dtExpire_time.AddDays(1)
            Else
                dtExpire_time = dtExpire_time.AddMinutes(30)
            End If
            ReDim oParam(2)
            sSQL = "insert into ACCOUNT_VERIFY  "
            sSQL = sSQL & "(ACCOUNT_ID,VERIFY_CODE,EXPIRE_TIME,VERIFY_TIME,VERIFY_IP,RESEND_VERIFYCODE) "
            sSQL = sSQL & "values (@id,@checkcode,@expire_time,'','',0) "
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = sID
            oParam(1) = New SqlClient.SqlParameter
            oParam(1).ParameterName = "@checkcode"
            oParam(1).Value = checkcode
            oParam(2) = New SqlClient.SqlParameter
            oParam(2).ParameterName = "@expire_time"
            oParam(2).Value = dtExpire_time
            strRt = oDB.ExecuteNonQuery(sSQL, oParam)
            If strRt <> "0" Then
                Throw New Exception(strRt)
            End If

            Return "0"  '產生會員激活驗證碼成功
        Catch ex As Exception
            Return ex.ToString
        End Try
    End Function

    ''' <summary>
    ''' 新增會員檔
    ''' </summary>
    ''' <param name="cname"></param>
    ''' <param name="email"></param>
    ''' <param name="mobileno"></param>
    ''' <param name="pwd"></param>
    ''' <param name="verifytype"></param>
    ''' <param name="regip"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddMember(ByVal cname As String, ByVal email As String, ByVal mobileno As String, ByVal pwd As String, ByVal verifytype As Integer, ByVal regip As String, ByVal birthday As String, ByVal sex As String, ByVal REFERER As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim strRt_ As String = String.Empty
        Dim dt As New DataTable
        Dim dt1 As DateTime
        Dim oParam() As SqlClient.SqlParameter
        ReDim oParam(0)
        oParam(0) = New SqlClient.SqlParameter
        oParam(0).ParameterName = "@mobileno"
        oParam(0).Value = mobileno

        Try
            '先判斷 電郵及手机號碼 是否為會員,是就不需新增
            sSQL = "select count(*) from accounts with (nolock) where mobile_no = @mobileno and mobile_no is not null and mobile_no <> ''  "
            dt = oDB.ExecuteDT(sSQL, oParam)
            If dt.Rows(0)(0) > 0 Then
                Return "1" '手机號碼已存在
            End If
            sSQL = "select count(*) from accounts with (nolock) where email = @email and email is not null and email <> '' "
            ReDim oParam(0)
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@email"
            oParam(0).Value = email
            dt = oDB.ExecuteDT(sSQL, oParam)
            If dt.Rows(0)(0) > 0 Then
                Return "2" '電郵已存在
            End If
            Erase oParam
            If (sex = "" And birthday = "" And REFERER = "") Then
                ReDim oParam(5)
                sSQL = "insert into ACCOUNTS "
                sSQL = sSQL & "(CNAME, TEL_NO, EMAIL, MOBILE_NO, STATUS, BILLING_DAY, "
                sSQL = sSQL & "BILLING_ADDRESS_ID, SMS_BILL, REG_TIME, REG_IP,REFERER, "
                sSQL = sSQL & "VERIFY_DATE,PASSWORD,NEED_RENEW,IS_LOCKED,VERIFY_TYPE,"
                sSQL = sSQL & "UPDATE_TIME,UPDATE_IP,LOGONFAIL)"
                sSQL = sSQL & "values (@cname,'',@email,@mobileno,0,'','','',getdate(),@regip,'',"
                sSQL = sSQL & "'',@pwd,0,'N',@verifytype,"
                sSQL = sSQL & "'','',0)"

                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@cname"
                oParam(0).Value = cname
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@email"
                oParam(1).Value = email
                oParam(2) = New SqlClient.SqlParameter
                oParam(2).ParameterName = "@mobileno"
                oParam(2).Value = mobileno
                oParam(3) = New SqlClient.SqlParameter
                oParam(3).ParameterName = "@pwd"
                oParam(3).Value = pwd
                oParam(4) = New SqlClient.SqlParameter
                oParam(4).ParameterName = "@verifytype"
                oParam(4).Value = verifytype
                oParam(5) = New SqlClient.SqlParameter
                oParam(5).ParameterName = "@regip"
                oParam(5).Value = regip
            ElseIf (sex = "" And birthday = "" And REFERER <> "") Then
                '檢查邀請人帳號是否存在,如果存在則統一轉換成EMAIL再填入資料表
                strRt_ = ACCOUNT_IS_EXIST(REFERER)
                If (strRt_ = "0") Then
                    REFERER = "service@minichucun.com"
                Else
                    REFERER = strRt_
                End If
                ReDim oParam(6)
                sSQL = "insert into ACCOUNTS "
                sSQL = sSQL & "(CNAME, TEL_NO, EMAIL, MOBILE_NO, STATUS, BILLING_DAY, "
                sSQL = sSQL & "BILLING_ADDRESS_ID, SMS_BILL, REG_TIME, REG_IP,REFERER, "
                sSQL = sSQL & "VERIFY_DATE,PASSWORD,NEED_RENEW,IS_LOCKED,VERIFY_TYPE,"
                sSQL = sSQL & "UPDATE_TIME,UPDATE_IP,LOGONFAIL)"
                sSQL = sSQL & "values (@cname,'',@email,@mobileno,0,'','','',getdate(),@regip,@referer,"
                sSQL = sSQL & "'',@pwd,0,'N',@verifytype,"
                sSQL = sSQL & "'','',0)"

                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@cname"
                oParam(0).Value = cname
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@email"
                oParam(1).Value = email
                oParam(2) = New SqlClient.SqlParameter
                oParam(2).ParameterName = "@mobileno"
                oParam(2).Value = mobileno
                oParam(3) = New SqlClient.SqlParameter
                oParam(3).ParameterName = "@pwd"
                oParam(3).Value = pwd
                oParam(4) = New SqlClient.SqlParameter
                oParam(4).ParameterName = "@verifytype"
                oParam(4).Value = verifytype
                oParam(5) = New SqlClient.SqlParameter
                oParam(5).ParameterName = "@regip"
                oParam(5).Value = regip
                oParam(6) = New SqlClient.SqlParameter
                oParam(6).ParameterName = "@referer"
                oParam(6).Value = REFERER
            ElseIf (sex <> "" And birthday = "" And REFERER <> "") Then
                ReDim oParam(7)
                sSQL = "insert into ACCOUNTS "
                sSQL = sSQL & "(CNAME, TEL_NO, EMAIL, MOBILE_NO, STATUS, BILLING_DAY, "
                sSQL = sSQL & "BILLING_ADDRESS_ID, SMS_BILL, REG_TIME, REG_IP,REFERER, "
                sSQL = sSQL & "VERIFY_DATE,PASSWORD,NEED_RENEW,IS_LOCKED,VERIFY_TYPE,"
                sSQL = sSQL & "UPDATE_TIME,UPDATE_IP,SEX,LOGONFAIL)"
                sSQL = sSQL & "values (@cname,'',@email,@mobileno,0,'','','',getdate(),@regip,@referer,"
                sSQL = sSQL & "'',@pwd,0,'N',@verifytype,"
                sSQL = sSQL & "'','',@sex,0)"

                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@cname"
                oParam(0).Value = cname
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@email"
                oParam(1).Value = email
                oParam(2) = New SqlClient.SqlParameter
                oParam(2).ParameterName = "@mobileno"
                oParam(2).Value = mobileno
                oParam(3) = New SqlClient.SqlParameter
                oParam(3).ParameterName = "@pwd"
                oParam(3).Value = pwd
                oParam(4) = New SqlClient.SqlParameter
                oParam(4).ParameterName = "@verifytype"
                oParam(4).Value = verifytype
                oParam(5) = New SqlClient.SqlParameter
                oParam(5).ParameterName = "@regip"
                oParam(5).Value = regip
                oParam(6) = New SqlClient.SqlParameter
                oParam(6).ParameterName = "@referer"
                oParam(6).Value = REFERER
                oParam(7) = New SqlClient.SqlParameter
                oParam(7).ParameterName = "@sex"
                oParam(7).Value = sex
            ElseIf (sex <> "" And birthday = "" And REFERER = "") Then
                ReDim oParam(6)
                sSQL = "insert into ACCOUNTS "
                sSQL = sSQL & "(CNAME, TEL_NO, EMAIL, MOBILE_NO, STATUS, BILLING_DAY, "
                sSQL = sSQL & "BILLING_ADDRESS_ID, SMS_BILL, REG_TIME, REG_IP,REFERER, "
                sSQL = sSQL & "VERIFY_DATE,PASSWORD,NEED_RENEW,IS_LOCKED,VERIFY_TYPE,"
                sSQL = sSQL & "UPDATE_TIME,UPDATE_IP,SEX,LOGONFAIL)"
                sSQL = sSQL & "values (@cname,'',@email,@mobileno,0,'','','',getdate(),@regip,'',"
                sSQL = sSQL & "'',@pwd,0,'N',@verifytype,"
                sSQL = sSQL & "'','',@sex,0)"

                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@cname"
                oParam(0).Value = cname
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@email"
                oParam(1).Value = email
                oParam(2) = New SqlClient.SqlParameter
                oParam(2).ParameterName = "@mobileno"
                oParam(2).Value = mobileno
                oParam(3) = New SqlClient.SqlParameter
                oParam(3).ParameterName = "@pwd"
                oParam(3).Value = pwd
                oParam(4) = New SqlClient.SqlParameter
                oParam(4).ParameterName = "@verifytype"
                oParam(4).Value = verifytype
                oParam(5) = New SqlClient.SqlParameter
                oParam(5).ParameterName = "@regip"
                oParam(5).Value = regip
                oParam(6) = New SqlClient.SqlParameter
                oParam(6).ParameterName = "@sex"
                oParam(6).Value = sex
            ElseIf (sex = "" And birthday <> "" And REFERER = "") Then
                ReDim oParam(6)
                sSQL = "insert into ACCOUNTS "
                sSQL = sSQL & "(CNAME, TEL_NO, EMAIL, MOBILE_NO, STATUS, BILLING_DAY, "
                sSQL = sSQL & "BILLING_ADDRESS_ID, SMS_BILL, REG_TIME, REG_IP,REFERER, "
                sSQL = sSQL & "VERIFY_DATE,PASSWORD,NEED_RENEW,IS_LOCKED,VERIFY_TYPE,"
                sSQL = sSQL & "UPDATE_TIME,UPDATE_IP,SEX,LOGONFAIL)"
                sSQL = sSQL & "values (@cname,'',@email,@mobileno,0,'','','',getdate(),@regip,'',"
                sSQL = sSQL & "'',@pwd,0,'N',@verifytype,"
                sSQL = sSQL & "'','',@birthday,0)"

                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@cname"
                oParam(0).Value = cname
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@email"
                oParam(1).Value = email
                oParam(2) = New SqlClient.SqlParameter
                oParam(2).ParameterName = "@mobileno"
                oParam(2).Value = mobileno
                oParam(3) = New SqlClient.SqlParameter
                oParam(3).ParameterName = "@pwd"
                oParam(3).Value = pwd
                oParam(4) = New SqlClient.SqlParameter
                oParam(4).ParameterName = "@verifytype"
                oParam(4).Value = verifytype
                oParam(5) = New SqlClient.SqlParameter
                oParam(5).ParameterName = "@regip"
                oParam(5).Value = regip
                oParam(6) = New SqlClient.SqlParameter
                oParam(6).ParameterName = "@birthday"
                dt1 = Convert.ToDateTime(birthday)
                oParam(6).Value = dt1
            Else
                ReDim oParam(7)
                sSQL = "insert into ACCOUNTS "
                sSQL = sSQL & "(CNAME, TEL_NO, EMAIL, MOBILE_NO, STATUS, BILLING_DAY, "
                sSQL = sSQL & "BILLING_ADDRESS_ID, SMS_BILL, REG_TIME, REG_IP,REFERER, "
                sSQL = sSQL & "VERIFY_DATE,PASSWORD,NEED_RENEW,IS_LOCKED,VERIFY_TYPE,"
                sSQL = sSQL & "UPDATE_TIME,UPDATE_IP,BIRTHDAY,SEX,LOGONFAIL)"
                sSQL = sSQL & "values (@cname,'',@email,@mobileno,0,'','','',getdate(),@regip,@referer,"
                sSQL = sSQL & "'',@pwd,0,'N',@verifytype,"
                sSQL = sSQL & "'','',@birthday,@sex,0)"

                oParam(0) = New SqlClient.SqlParameter
                oParam(0).ParameterName = "@cname"
                oParam(0).Value = cname
                oParam(1) = New SqlClient.SqlParameter
                oParam(1).ParameterName = "@email"
                oParam(1).Value = email
                oParam(2) = New SqlClient.SqlParameter
                oParam(2).ParameterName = "@mobileno"
                oParam(2).Value = mobileno
                oParam(3) = New SqlClient.SqlParameter
                oParam(3).ParameterName = "@pwd"
                oParam(3).Value = pwd
                oParam(4) = New SqlClient.SqlParameter
                oParam(4).ParameterName = "@verifytype"
                oParam(4).Value = verifytype
                oParam(5) = New SqlClient.SqlParameter
                oParam(5).ParameterName = "@referer"
                oParam(5).Value = REFERER
                oParam(6) = New SqlClient.SqlParameter
                oParam(6).ParameterName = "@regip"
                oParam(6).Value = regip
                oParam(7) = New SqlClient.SqlParameter
                oParam(7).ParameterName = "@birthday"
                dt1 = Convert.ToDateTime(birthday)
                oParam(7).Value = dt1
                oParam(8) = New SqlClient.SqlParameter
                oParam(8).ParameterName = "@sex"
                oParam(8).Value = sex
            End If

            strRt = oDB.ExecuteNonQuery(sSQL, oParam)
            If strRt <> "0" Then
                Throw New Exception(strRt)
            End If
            Return "0"  '加入會員成功
        Catch ex As Exception
            Return ex.ToString
        End Try
    End Function

    ''' <summary>
    ''' 變更密碼
    ''' </summary>
    ''' <param name="id"></param>
    ''' <param name="newPwd"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function ChangePwd(ByVal id As String, ByVal newPwd As String, ByVal usrip As String) As String
        Dim sSQL As String = String.Empty
        Dim strRt As String = String.Empty
        Dim oParam() As SqlClient.SqlParameter
        Try
            ReDim oParam(2)
            sSQL = "UPDATE ACCOUNTS SET PASSWORD = @newPwd,UPDATE_TIME = GETDATE(),NEED_RENEW = '0',UPDATE_IP = @usrip where ID = @id"
            oParam(0) = New SqlClient.SqlParameter
            oParam(0).ParameterName = "@id"
            oParam(0).Value = id
            oParam(1) = New SqlClient.SqlParameter
            oParam(1).ParameterName = "@newPwd"
            oParam(1).Value = newPwd
            oParam(2) = New SqlClient.SqlParameter
            oParam(2).ParameterName = "@usrip"
            oParam(2).Value = usrip
            strRt = oDB.ExecuteNonQuery(sSQL, oParam)
            If strRt <> "0" Then
                Throw New Exception(strRt)
            End If
            strRt = "0"
        Catch ex As Exception
            Return ex.ToString
        End Try
        Return strRt
    End Function


    Protected Overrides Sub Finalize()
        oDB = Nothing
        MyBase.Finalize()
    End Sub

    Public Sub New()
        oDB = New clsDBUtil
    End Sub



End Class
